﻿Public Class Clasificacion
    Dim con As New Conexion_SQL
    Dim dtRes As DataTable
    Dim mostrada As Boolean = False

    Dim dtCuentas As DataTable

    Dim tipo_mov As String = ""
    Dim cuenta As String = ""
    Dim idConcepto As Integer = 0
    Dim idSubConcepto As String = ""
    Dim referencia As String = ""
    Dim empresa As Integer = 0
    Dim idDepto As Integer = 0
    Dim base As String = ""
    Dim ctaCargo As String = ""
    Dim ctaRetiro As String = ""
    Dim int_sae As Boolean = False

    Dim modificando As Boolean = False


    Private Sub Clasificacion_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Carga_estados()

        Dim cad As String
        cad = "select CVE_CTA, 1 AS EMP, 'BANPAP' AS EMPRESA ,NUM_CTA, BANCO, CTA_CONTAB from BANPAP.dbo.CTAS01 "
        cad += "UNION ALL "
        cad += "select CVE_CTA, 2 AS EMP, 'BANSER' AS EMPRESA ,NUM_CTA, BANCO, CTA_CONTAB from BANSER.dbo.CTAS02 "

        dtCuentas = New DataTable
        con.OpenQueryIntoDataTable(dtCuentas, cad)
    End Sub

    Private Sub Carga_estados()
        dgvRes.DataSource = Nothing

        con.ConnectDataBase(cst_Papalote)
        dtRes = New DataTable
        Dim cad As String = "SELECT * FROM ECUENTAS WHERE STATUS=1 AND TRASPASO=0 AND CHEQUE=0 ORDER BY ID"

        con.OpenQueryIntoDataTable(dtRes, cad)

        For Each col As DataColumn In dtRes.Columns
            If col.Ordinal <= 11 Then
                Dim tit As String = col.ColumnName.ToLower
                tit = tit.Substring(0, 1).ToUpper & tit.Substring(1)
                col.ColumnName = tit
            End If
        Next

        dtRes.Columns("Rtexto").ColumnName = "Ref_Alfanumerica"
        dtRes.Columns("Rnumero").ColumnName = "Ref_Numerica"

        dgvRes.DataSource = dtRes

        dgvRes.Visible = True


        dgvRes.Columns(0).Visible = False

        For i As Integer = 12 To dgvRes.Columns.Count - 1
            dgvRes.Columns(i).Visible = False
        Next

        dgvRes.Columns("Depositos").DefaultCellStyle.Format = "$ ###,###,##0.00"
        dgvRes.Columns("Depositos").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgvRes.Columns("Retiros").DefaultCellStyle.Format = "$ ###,###,##0.00"
        dgvRes.Columns("Retiros").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
    End Sub

    Private Sub Clasificacion_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
        If dgvRes.SelectedRows.Count > 0 Then dgvRes.SelectedRows(0).Selected = False
        mostrada = True
    End Sub

    Private Sub dgvRes_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvRes.KeyDown
        If e.KeyCode = Keys.Space AndAlso dgvRes.SelectedRows.Count > 0 Then
            Carga_Datos(True)
        End If
    End Sub

    Private Sub Carga_Datos(ByVal modif As Boolean)
        txtClasificacion.Text = ""
        lbClasificacion.Text = ""
        lbClasificacionDetalle.Text = ""

        txtClasificacion2.Text = ""
        lbClasificacion2.Text = ""
        lbClasificacion2Detalle.Text = ""

        txtClasificacion2.Text = ""
        lbClasificacion2.Text = ""
        lbClasificacion2Detalle.Text = ""

        txtAbono.Text = ""
        lbAbono.Text = ""
        txtCargo.Text = ""
        lbCargo.Text = ""


        gbClasificacion.Visible = True

        modificando = modif

        cuenta = dgvRes.SelectedRows(0).Cells("Cuenta").Value

        empresa = CType(dtCuentas.Select("NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("EMP")
        base = CType(dtCuentas.Select("NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("EMPRESA")

        idSubConcepto = ""
        referencia = ""

        int_sae = False

        If dgvRes.SelectedRows(0).Cells("Concepto").Value Is DBNull.Value Then
            idConcepto = 0
        Else
            idConcepto = dgvRes.SelectedRows(0).Cells("Concepto").Value
            Carga_Concepto(idConcepto, int_sae)
        End If

        If int_sae Then
            If dgvRes.SelectedRows(0).Cells("Concepto2").Value Is DBNull.Value Then
                idSubConcepto = ""
            Else
                idSubConcepto = dgvRes.SelectedRows(0).Cells("Concepto2").Value
                Carga_SubConcepto(idSubConcepto, tipo_mov)
            End If

            If dgvRes.SelectedRows(0).Cells("Ref").Value Is DBNull.Value Then
                referencia = ""
            Else
                referencia = dgvRes.SelectedRows(0).Cells("Ref").Value
                If idSubConcepto.Trim.Length > 0 Then
                    Carga_Referencia(referencia, tipo_mov, idSubConcepto)
                End If
            End If

            If modificando Then
                txtClasificacion2.Visible = True
                lbClasificacion2.Visible = False

                txtClasificacion3.Visible = True
                lbClasificacion3.Visible = False
            Else
                txtClasificacion2.Visible = False
                lbClasificacion2.Visible = True

                txtClasificacion3.Visible = False
                lbClasificacion3.Visible = True
            End If
        Else
            txtClasificacion2.Text = ""
            lbClasificacion2.Text = ""
            lbClasificacion2Detalle.Text = ""

            txtClasificacion3.Text = ""
            lbClasificacion3.Text = ""
            lbClasificacion3Detalle.Text = ""

            txtClasificacion2.Visible = False
            lbClasificacion2.Visible = False
            lbClasificacion2Detalle.Visible = False

            txtClasificacion3.Visible = False
            lbClasificacion3.Visible = False
            lbClasificacion3Detalle.Visible = False
        End If

        If modificando Then
            txtClasificacion.Visible = True
            lbClasificacion.Visible = False
            cbxPoliza.Enabled = True
        Else
            txtClasificacion.Visible = False
            lbClasificacion.Visible = True
            cbxPoliza.Enabled = False
        End If

        cbxPoliza.Checked = dgvRes.SelectedRows(0).Cells("Poliza").Value

        If cbxPoliza.Checked Then
            If dgvRes.SelectedRows(0).Cells("Cargo").Value Is DBNull.Value Then
                txtCargo.Text = Crea_Cuenta("0")
            Else
                txtCargo.Text = Crea_Cuenta(dgvRes.SelectedRows(0).Cells("Cargo").Value)
            End If

            If dgvRes.SelectedRows(0).Cells("Abono").Value Is DBNull.Value Then
                txtAbono.Text = Crea_Cuenta("0")
            Else
                txtAbono.Text = Crea_Cuenta(dgvRes.SelectedRows(0).Cells("Abono").Value)
            End If

            If dgvRes.SelectedRows(0).Cells("Depto").Value Is DBNull.Value Then
                txtDepto.Text = 0
            Else
                txtDepto.Text = dgvRes.SelectedRows(0).Cells("Depto").Value
                Carga_Departamento(CInt(txtDepto.Text))
            End If

            lbCargo.Text = txtCargo.Text
            lbAbono.Text = txtAbono.Text
            lbDepto.Text = txtDepto.Text

            If modificando Then
                txtCargo.Visible = True
                txtAbono.Visible = True
                txtDepto.Visible = True

                lbCargo.Visible = False
                lbAbono.Visible = False
                lbDepto.Visible = False
            Else
                txtCargo.Visible = False
                txtAbono.Visible = False
                txtDepto.Visible = False

                lbCargo.Visible = True
                lbAbono.Visible = True
                lbDepto.Visible = True
            End If
        Else
            txtCargo.Visible = False
            txtAbono.Visible = False
            txtDepto.Visible = False

            lbCargo.Visible = False
            lbAbono.Visible = False
            lbDepto.Visible = False

            Label15.Visible = False
            Label2.Visible = False
            Label20.Visible = False
            Label3.Visible = False
        End If

        If modif Then
            btnActualizar.Visible = True
            txtClasificacion.SelectAll()
            txtClasificacion.Focus()
        Else
            btnActualizar.Visible = False
            dgvRes.Focus()
        End If

    End Sub

    Private Sub dgvRes_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvRes.SelectionChanged
        If mostrada AndAlso dgvRes.SelectedRows.Count > 0 Then
            gbDatos.Controls.Clear()

            Dim y As Integer = 15
            Dim alto As Integer = 21
            Dim agregado As Boolean = False


            For i As Integer = 1 To 9
                If Not (dtRes.Columns(i).ColumnName = "Depositos" Or dtRes.Columns(i).ColumnName = "Retiros") Then
                    Dim lbTit As New Label
                    lbTit.AutoSize = False
                    lbTit.Size = New Size(gbDatos.Width / 3, alto)
                    lbTit.Font = New Font("Tahoma", 9, FontStyle.Bold)
                    lbTit.TextAlign = ContentAlignment.MiddleLeft
                    lbTit.Location = New Point(3, y)
                    lbTit.Text = dtRes.Columns(i).ColumnName & ":"

                    Dim lbDat As New Label
                    lbDat.AutoSize = False
                    lbDat.Size = New Size(((gbDatos.Width / 3) * 2) - 6, alto)
                    lbDat.Font = New Font("Tahoma", 9, FontStyle.Regular)
                    lbDat.TextAlign = ContentAlignment.MiddleLeft
                    lbDat.Location = New Point(gbDatos.Width / 3, y)
                    lbDat.Text = dtRes.Rows(dgvRes.SelectedRows(0).Index)(i)
                    lbDat.BackColor = Color.LightYellow

                    y += alto + 2
                    gbDatos.Controls.Add(lbTit)
                    gbDatos.Controls.Add(lbDat)

                ElseIf agregado = False Then
                    Dim tit As String
                    Dim valor As Double
                    If dtRes.Rows(dgvRes.SelectedRows(0).Index)("Depositos") = 0 Then
                        tit = "Retiro"
                        lbClas2.Text = "Proveedor"
                        valor = dtRes.Rows(dgvRes.SelectedRows(0).Index)("Retiros")
                        tipo_mov = "C"
                    Else
                        tit = "Deposito"
                        lbClas2.Text = "Cliente"
                        valor = dtRes.Rows(dgvRes.SelectedRows(0).Index)("Depositos")
                        tipo_mov = "A"
                    End If

                    Dim lbTit As New Label
                    lbTit.AutoSize = False
                    lbTit.Size = New Size(gbDatos.Width / 3, alto)
                    lbTit.Font = New Font("Tahoma", 9, FontStyle.Bold)
                    lbTit.TextAlign = ContentAlignment.MiddleLeft
                    lbTit.Location = New Point(3, y)
                    lbTit.Text = tit & ":"

                    Dim lbDat As New Label
                    lbDat.AutoSize = False
                    lbDat.Size = New Size(((gbDatos.Width / 3) * 2) - 6, alto)
                    lbDat.Font = New Font("Tahoma", 9, FontStyle.Regular)
                    lbDat.TextAlign = ContentAlignment.MiddleLeft
                    lbDat.Location = New Point(gbDatos.Width / 3, y)
                    lbDat.Text = Format(valor, "$ ###,###,##0.00")
                    lbDat.BackColor = Color.LightYellow

                    y += alto + 2
                    gbDatos.Controls.Add(lbTit)
                    gbDatos.Controls.Add(lbDat)
                    agregado = True
                End If
            Next

            Dim cbTrasp As New CheckBox
            cbTrasp.Text = "Traspaso:"
            cbTrasp.TextAlign = ContentAlignment.MiddleLeft
            cbTrasp.CheckAlign = ContentAlignment.MiddleRight
            cbTrasp.Font = New Font("Tahoma", 9, FontStyle.Bold)
            cbTrasp.Checked = dtRes.Rows(dgvRes.SelectedRows(0).Index)("Traspaso")
            cbTrasp.Location = New Point(3, y)
            cbTrasp.AutoSize = False
            cbTrasp.Size = New Size((gbDatos.Width / 3) + 12, alto)
            cbTrasp.Enabled = False
            y += alto + 2
            gbDatos.Controls.Add(cbTrasp)

            Dim cbCheque As New CheckBox
            cbCheque.Text = "Cheque:"
            cbCheque.TextAlign = ContentAlignment.MiddleLeft
            cbCheque.CheckAlign = ContentAlignment.MiddleRight
            cbCheque.Font = New Font("Tahoma", 9, FontStyle.Bold)
            cbCheque.Checked = dtRes.Rows(dgvRes.SelectedRows(0).Index)("Cheque")
            cbCheque.Location = New Point(3, y)
            cbCheque.AutoSize = False
            cbCheque.Size = New Size((gbDatos.Width / 3) + 12, alto)
            cbCheque.Enabled = False
            y += alto + 2
            gbDatos.Controls.Add(cbCheque)


            gbDatos.Visible = True

            Carga_Datos(False)

            dgvRes.Focus()
        End If
    End Sub

#Region "Clasificacion"
    Private Sub txtClasificacion_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtClasificacion.KeyDown
        If e.KeyCode = Keys.Space Or e.KeyCode = Keys.Delete Then
            txtClasificacion.Text = UCase(txtClasificacion.Text).Trim

            Dim cantidad As Integer = con.QueryGetFieldTmpCnn("SELECT COUNT(*) FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE TIPO='" & tipo_mov & "' AND CVE_CONCEP LIKE '%" & txtClasificacion.Text & "%'", cst_SAE)

            Dim cat As New Catalogo
            cat.Text = "Concepto"

            Dim tmpConcepto As Integer = idConcepto

            If cantidad = 1 Then
                idConcepto = con.QueryGetFieldTmpCnn("SELECT NUM_REG FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE TIPO='" & tipo_mov & "' AND CVE_CONCEP LIKE '%" & txtClasificacion.Text & "%'", cst_SAE)
            ElseIf cantidad = 0 Then
                idConcepto = cat.Mostrar("SELECT NUM_REG, '(' + CVE_CONCEP + ') ' + CONCEP FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE TIPO='" & tipo_mov & "'", cst_SAE, idConcepto)
            Else
                idConcepto = cat.Mostrar("SELECT NUM_REG, '(' + CVE_CONCEP + ') ' + CONCEP FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE TIPO='" & tipo_mov & "' AND CVE_CONCEP LIKE '%" & txtClasificacion.Text & "%'", cst_SAE, idConcepto)
            End If

            'Dim abo_sae As Boolean = False

            Carga_Concepto(idConcepto, int_sae)

            If idConcepto <> tmpConcepto Then
                txtClasificacion2.Text = ""
                lbClasificacion2.Text = ""
                lbClasificacion2Detalle.Text = ""

                txtClasificacion3.Text = ""
                lbClasificacion3.Text = ""
                lbClasificacion3Detalle.Text = ""
            End If

            If int_sae Then
                txtClasificacion2.Visible = True
                lbClasificacion2.Visible = False
                lbClasificacion2Detalle.Visible = True

                Carga_SubConcepto(idSubConcepto, tipo_mov)

                txtClasificacion3.Visible = True
                lbClasificacion3.Visible = False
                lbClasificacion3Detalle.Visible = True

                Carga_Referencia(referencia, tipo_mov, idSubConcepto)
            Else
                txtClasificacion2.Visible = False
                lbClasificacion2.Visible = False
                lbClasificacion2Detalle.Visible = False

                txtClasificacion3.Visible = False
                lbClasificacion3.Visible = False
                lbClasificacion3Detalle.Visible = False
            End If

        ElseIf e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            If txtClasificacion2.Visible Then
                txtClasificacion2.Focus()
                txtClasificacion2.SelectAll()
            Else
                If Valida_Datos() Then
                    cbxPoliza.Focus()
                End If
            End If
        End If
    End Sub

    Private Sub Carga_Concepto(ByVal ide As Integer, ByRef abo_sae As Boolean)
        txtClasificacion.Text = ""
        lbClasificacion.Text = ""
        lbClasificacionDetalle.Text = ""

        'con.ConnectDataBase(cst_SAE)

        If ide > 0 Then
            con.OpenQuery("SELECT CVE_CONCEP, CONCEP, ABO_SAE FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE NUM_REG=" & ide)
            If con.Dr.Read Then
                txtClasificacion.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbClasificacion.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbClasificacionDetalle.Text = con.Dr.GetValue(1).ToString.ToUpper.Trim
                If con.Dr.GetValue(2).ToString.Trim = "N" Then
                    abo_sae = False
                Else
                    abo_sae = True
                End If

            End If
            con.DisconnectDataReader()

            If abo_sae Then

            End If


            If abo_sae Then
                txtClasificacion2.Visible = True
                lbClasificacion2.Visible = False
                lbClasificacion2Detalle.Visible = True

                Carga_SubConcepto(idSubConcepto, tipo_mov)

                txtClasificacion3.Visible = True
                lbClasificacion3.Visible = False
                lbClasificacion3Detalle.Visible = True

                Carga_Referencia(referencia, tipo_mov, idSubConcepto)
            Else
                txtClasificacion2.Visible = False
                lbClasificacion2.Visible = False
                lbClasificacion2Detalle.Visible = False

                txtClasificacion3.Visible = False
                lbClasificacion3.Visible = False
                lbClasificacion3Detalle.Visible = False
            End If

            If txtClasificacion2.Visible Then
                txtClasificacion2.Focus()
                txtClasificacion2.SelectAll()
            Else
                If Valida_Datos() Then
                    cbxPoliza.Focus()
                End If
            End If
        End If
    End Sub
#End Region

#Region "SubConcepto"
    Private Sub txtClasificacion2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtClasificacion2.KeyDown
        If e.KeyCode = Keys.Space Or e.KeyCode = Keys.Delete Then
            txtClasificacion2.Text = UCase(txtClasificacion2.Text).Trim

            Dim clave As String = ""
            Dim tabla As String = ""

            If tipo_mov = "C" Then
                clave = "CPROV"
                tabla = "PROV01"
            Else
                clave = "CCLIE"
                tabla = "CLIE01"
            End If

            Dim cantidad As Integer = con.QueryGetFieldTmpCnn("SELECT COUNT(*) FROM " & tabla & " WHERE STATUS='A' and LTRIM(RTRIM(" & clave & ")) LIKE '%" & txtClasificacion2.Text & "%'", cst_SAE)

            Dim cat As New Catalogo
            cat.Text = "Concepto"

            Dim tmpSubConcepto As String = idSubConcepto

            If cantidad = 1 Then
                idSubConcepto = con.QueryGetFieldTmpCnn("SELECT LTRIM(RTRIM(" & clave & ")) FROM " & tabla & " WHERE STATUS='A' and LTRIM(RTRIM(" & clave & ")) LIKE '%" & txtClasificacion2.Text & "%'", cst_SAE)
            ElseIf cantidad = 0 Then
                idSubConcepto = cat.Mostrar("SELECT LTRIM(RTRIM(" & clave & ")),'(' + LTRIM(RTRIM(" & clave & ")) + ') ' +NOMBRE FROM " & tabla & " WHERE STATUS='A'", cst_SAE, idConcepto)
            Else
                idSubConcepto = cat.Mostrar("SELECT LTRIM(RTRIM(" & clave & ")),'(' + LTRIM(RTRIM(" & clave & ")) + ') ' +NOMBRE FROM " & tabla & " WHERE STATUS='A' and LTRIM(RTRIM(" & clave & ")) LIKE '%" & txtClasificacion2.Text & "%'", cst_SAE, idConcepto)
            End If

            If idSubConcepto <> tmpSubConcepto Then
                txtClasificacion3.Text = ""
                lbClasificacion3.Text = ""
                lbClasificacion3Detalle.Text = ""
            End If

            Carga_SubConcepto(idSubConcepto, tipo_mov)
        ElseIf e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            txtClasificacion3.Focus()
            txtClasificacion3.SelectAll()
        End If
    End Sub

    Private Sub Carga_SubConcepto(ByVal ide As String, ByVal tipo As String)
        txtClasificacion2.Text = ""
        lbClasificacion2.Text = ""
        lbClasificacion2Detalle.Text = ""

        Dim clave As String = ""
        Dim tabla As String = ""

        If ide.Trim.Length > 0 Then

            If tipo_mov = "C" Then
                clave = "CPROV"
                tabla = "PROV01"
            Else
                clave = "CCLIE"
                tabla = "CLIE01"
            End If

            con.ConnectDataBase(cst_SAE)
            con.OpenQuery("SELECT LTRIM(RTRIM(" & clave & ")),NOMBRE FROM " & tabla & " WHERE STATUS='A' and LTRIM(RTRIM(" & clave & "))='" & ide.Trim & "'")
            If con.Dr.Read Then
                txtClasificacion2.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbClasificacion2.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbClasificacion2Detalle.Text = con.Dr.GetValue(1).ToString.ToUpper.Trim
            End If
            con.DisconnectDataReader()

            txtClasificacion3.Focus()
            txtClasificacion3.SelectAll()
        End If
    End Sub
#End Region

#Region "Referencia"
    Private Sub txtClasificacion3_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtClasificacion3.KeyDown
        If e.KeyCode = Keys.Space Or e.KeyCode = Keys.Delete Then
            txtClasificacion3.Text = UCase(txtClasificacion3.Text).Trim

            Dim clave As String = ""
            Dim tabla As String = ""

            If tipo_mov = "C" Then
                clave = "CPROV"
                tabla = "PAGA01"
            Else
                clave = "CCLIE"
                tabla = "CUEN01"
            End If

            Dim cantidad As Integer = con.QueryGetFieldTmpCnn("SELECT COUNT(*) FROM " & tabla & " WHERE TIPO_MOV=1 AND LTRIM(RTRIM(" & clave & "))='" & idSubConcepto & "' AND LTRIM(REFER) LIKE '%" & txtClasificacion3.Text & "%'", cst_SAE)

            Dim cat As New Catalogo
            cat.Text = "Concepto"

            If cantidad = 1 Then
                referencia = con.QueryGetFieldTmpCnn("SELECT LTRIM(REFER) FROM " & tabla & " WHERE TIPO_MOV=1 AND LTRIM(RTRIM(" & clave & "))='" & idSubConcepto & "' AND LTRIM(REFER) LIKE '%" & txtClasificacion3.Text & "%'", cst_SAE)
            ElseIf cantidad = 0 Then
                referencia = cat.Mostrar("SELECT LTRIM(REFER), '(' + LTRIM(REFER) + ')  ' + convert(char(12), FECHA_APLI,107) + '  -  $ ' + CAST(CAST(IMPORTE AS MONEY)AS CHAR(10)) FROM " & tabla & " WHERE TIPO_MOV=1 AND LTRIM(RTRIM(" & clave & "))='" & idSubConcepto & "'", cst_SAE, idConcepto)
            Else
                referencia = cat.Mostrar("SELECT LTRIM(REFER), '(' + LTRIM(REFER) + ')  ' + convert(char(12), FECHA_APLI,107) + '  -  $ ' + CAST(CAST(IMPORTE AS MONEY)AS CHAR(10)) FROM " & tabla & " WHERE TIPO_MOV=1 AND LTRIM(RTRIM(" & clave & "))='" & idSubConcepto & "' AND LTRIM(REFER) LIKE '%" & txtClasificacion3.Text & "%'", cst_SAE, idConcepto)
            End If

            Carga_Referencia(referencia, tipo_mov, idSubConcepto)
        ElseIf e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            If Valida_Datos() Then
                cbxPoliza.Focus()
            End If
        End If
    End Sub

    Private Sub Carga_Referencia(ByVal ide As String, ByVal tipo As String, ByVal ideC As String)
        txtClasificacion3.Text = ""
        lbClasificacion3.Text = ""
        lbClasificacion3Detalle.Text = ""

        Dim clave As String = ""
        Dim tabla As String = ""

        If ide.Trim.Length > 0 Then

            If tipo_mov = "C" Then
                clave = "CPROV"
                tabla = "PAGA01"
            Else
                clave = "CCLIE"
                tabla = "CUEN01"
            End If

            con.ConnectDataBase(cst_SAE)
            con.OpenQuery("SELECT LTRIM(REFER), convert(char(12), FECHA_APLI,107),CAST(IMPORTE AS MONEY) FROM " & tabla & " WHERE TIPO_MOV=1 AND LTRIM(RTRIM(" & clave & "))='" & ideC & "' AND LTRIM(REFER)='" & ide.Trim & "'")
            If con.Dr.Read Then
                txtClasificacion3.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbClasificacion3.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbClasificacion3Detalle.Text = con.Dr.GetValue(1).ToString.ToUpper.Trim & "   (" & Format(con.Dr.GetValue(2), "$ ###,###,##0.00") & ")"
            End If
            con.DisconnectDataReader()

            If Valida_Datos() Then
                cbxPoliza.Focus()
            End If
        End If
    End Sub
#End Region

#Region "Departamento"
    Private Sub txtDepto_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtDepto.KeyDown
        If e.KeyCode = Keys.Space Or e.KeyCode = Keys.Delete Then
            Dim cantidad As Integer = con.QueryGetFieldTmpCnn("select count(*) from Papalote.dbo.departamentos where id_departamento=" & txtDepto.Text, cst_Papalote)

            Dim cat As New Catalogo
            cat.Text = "Concepto"

            Dim tmpConcepto As Integer = idConcepto

            If cantidad = 1 Then
                idDepto = con.QueryGetFieldTmpCnn("select id_departamento from Papalote.dbo.departamentos where id_departamento=" & txtDepto.Text, cst_SAE)
            Else
                idDepto = cat.Mostrar("select id_departamento from Papalote.dbo.departamentos order by id_departamento", cst_Papalote, txtDepto.Text)
            End If

            Carga_Departamento(idDepto)
        ElseIf e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            btnActualizar.Focus()
        End If
    End Sub

    Private Sub txtDepto_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDepto.KeyPress
        If Char.IsDigit(e.KeyChar) Then
            e.Handled = False
        Else
            e.Handled = True
        End If
    End Sub

    Private Sub Carga_Departamento(ByVal ide As Integer)
        txtDepto.Text = ""
        lbDepto.Text = ""
        lbDeptoDetalle.Text = ""

        'con.ConnectDataBase(cst_Papalote)
        If ide > 0 Then
            con.OpenQuery("select id_departamento, departamento from Papalote.dbo.departamentos where id_departamento=" & ide)
            If con.Dr.Read Then
                txtDepto.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbDepto.Text = con.Dr.GetValue(0).ToString.ToUpper.Trim
                lbDeptoDetalle.Text = con.Dr.GetValue(1).ToString.ToUpper.Trim
            End If
            con.DisconnectDataReader()
        End If

    End Sub
#End Region

    Private Sub cbxPoliza_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxPoliza.CheckStateChanged
        Muestra_Cuentas()
    End Sub

    Private Sub cbxPoliza_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxPoliza.GotFocus
        If modificando Then
            cbxPoliza.ForeColor = Color.DarkRed
        End If
    End Sub

    Private Sub cbxPoliza_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cbxPoliza.KeyDown
        If e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            If cbxPoliza.Checked = True Then
                txtCargo.Focus()
                txtCargo.SelectAll()
            Else
                btnActualizar.Focus()
            End If
        End If
    End Sub

    Private Sub cbxPoliza_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxPoliza.LostFocus
        If modificando Then
            cbxPoliza.ForeColor = Color.Black
        End If
    End Sub

    Private Function Valida_Datos() As Boolean
        Dim valores(2) As Boolean

        If con.QueryGetFieldTmpCnn("SELECT count(*) FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE NUM_REG=" & idConcepto, cst_SAE) > 0 Then
            valores(0) = True
        End If

        If int_sae Then
            Dim clave As String = ""
            Dim tabla As String = ""
            Dim tabla2 As String = ""

            If tipo_mov = "C" Then
                clave = "CPROV"
                tabla = "PROV01"
                tabla2 = "PAGA01"
            Else
                clave = "CCLIE"
                tabla = "CLIE01"
                tabla2 = "CUEN01"
            End If
            If con.QueryGetFieldTmpCnn("SELECT count(*) FROM " & tabla & " WHERE STATUS='A' and LTRIM(RTRIM(" & clave & "))='" & idSubConcepto.Trim & "'", cst_SAE) Then
                valores(1) = True
            End If

            If con.QueryGetFieldTmpCnn("SELECT count(*) FROM " & tabla2 & " WHERE TIPO_MOV=1 AND LTRIM(RTRIM(" & clave & "))='" & idSubConcepto & "' AND LTRIM(REFER)='" & referencia.Trim & "'", cst_SAE) Then
                valores(2) = True
            End If
        Else
            valores(1) = True
            valores(2) = True
        End If

        If valores(0) = False Then
            MessageBox.Show("La clasificación no es valida!", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtClasificacion.Focus()
            txtClasificacion.SelectAll()
            Application.DoEvents()
            Return False
            Exit Function
        End If

        If valores(1) = False Then
            MessageBox.Show("La clave del " & lbClas2.Text & " no es valida!", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtClasificacion2.Focus()
            txtClasificacion2.SelectAll()
            Application.DoEvents()
            Return False
            Exit Function
        End If

        If valores(2) = False Then
            MessageBox.Show("La referencia del documento no es valida!", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtClasificacion3.Focus()
            txtClasificacion3.SelectAll()
            Application.DoEvents()
            Return False
            Exit Function
        End If

        Return True
    End Function

    Private Sub Muestra_Cuentas()
        Dim visible As Boolean = cbxPoliza.Checked
        Label15.Visible = visible
        Label2.Visible = visible
        Label20.Visible = visible
        Label3.Visible = visible
        lbAbono.Visible = visible
        lbCargo.Visible = visible
        lbDepto.Visible = visible
        lbDeptoDetalle.Visible = visible
        txtCargo.Visible = visible
        txtAbono.Visible = visible
        txtDepto.Visible = visible

        If modificando And visible Then
            txtAbono.Visible = visible
            txtCargo.Visible = visible
            txtDepto.Visible = visible

            lbAbono.Visible = False
            lbCargo.Visible = False
            lbDepto.Visible = False
        End If

        If base.Trim.Length > 0 And empresa > 0 And idConcepto > 0 Then
            Dim valor As Object = con.QueryGetFieldTmpCnn("SELECT CTA_CONTAB FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE NUM_REG=" & idConcepto)
            If valor Is DBNull.Value Then valor = 0

            If tipo_mov = "C" Then
                txtCargo.Text = Formatea_Cuenta(valor)
                txtAbono.Text = Formatea_Cuenta(CType(dtCuentas.Select("EMP=" & empresa & " AND NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("CTA_CONTAB"))
            Else
                txtAbono.Text = Formatea_Cuenta(valor)
                txtCargo.Text = Formatea_Cuenta(CType(dtCuentas.Select("EMP=" & empresa & " AND NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("CTA_CONTAB"))
            End If

            lbAbono.Text = txtAbono.Text
            lbCargo.Text = txtCargo.Text

            txtDepto.Text = ""
            lbDepto.Text = ""
            lbDeptoDetalle.Text = ""
        End If
    End Sub

    Private Sub txtCargo_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCargo.KeyDown
        If e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            txtAbono.Focus()
            txtAbono.SelectAll()
        End If
    End Sub

    Private Sub txtAbono_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAbono.KeyDown
        If e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            txtDepto.Focus()
        End If
    End Sub

    Private Sub btnActualizar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActualizar.Click
        If idConcepto = 0 Or txtCargo.Text.Trim.Length = 0 Or txtAbono.Text.Trim.Length = 0 Or txtDepto.Text.Trim.Length = 0 Then

        End If
        Dim ind As Integer = dgvRes.SelectedRows(0).Index
        dtRes.Rows(ind)("Concepto") = idConcepto
        dtRes.Rows(ind)("Isae") = int_sae
        dtRes.Rows(ind)("Concepto2") = idSubConcepto
        dtRes.Rows(ind)("Ref") = referencia
        dtRes.Rows(ind)("Poliza") = cbxPoliza.Checked
        dtRes.Rows(ind)("Cargo") = Crea_Cuenta(txtCargo.Text)
        dtRes.Rows(ind)("Abono") = Crea_Cuenta(txtAbono.Text)
        dtRes.Rows(ind)("Depto") = txtDepto.Text


        Dim fila As DataRow = dtRes.Rows(ind)
        If Not (fila("Concepto") Is DBNull.Value OrElse fila("Concepto").ToString.Trim.Length = 0) Then
            Dim isae As Integer
            If fila("Isae") = True Then
                isae = 1
            Else
                isae = 0
            End If

            Dim poliza As Integer
            If fila("Poliza") = True Then
                poliza = 1
            Else
                poliza = 1
            End If

            con.ConnectDataBase(cst_Papalote)
            con.ExecuteSQL("UPDATE ECUENTAS SET CONCEPTO=" & fila("Concepto") & ", ISAE=" & isae.ToString & ", CONCEPTO2='" & fila("Concepto2") & "', REF='" & fila("Ref") & "', POLIZA=" & poliza.ToString & ", CARGO='" & fila("Cargo") & "' , ABONO='" & fila("Abono") & "', DEPTO=" & fila("Depto") & " WHERE ID=" & fila(0))
        End If


        dgvRes.Refresh()

        dgvRes.Focus()


        Carga_Datos(False)
    End Sub

    Private Sub btnGuardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardar.Click
        con.ConnectDataBase(cst_Papalote)

        For Each fila As DataRow In dtRes.Rows
            If Not (fila("Concepto") Is DBNull.Value OrElse fila("Concepto").ToString.Trim.Length = 0) Then
                Dim isae As Integer
                If fila("Isae") = True Then
                    isae = 1
                Else
                    isae = 0
                End If

                Dim poliza As Integer
                If fila("Poliza") = True Then
                    poliza = 1
                Else
                    poliza = 1
                End If

                con.ExecuteSQL("UPDATE ECUENTAS SET CONCEPTO=" & fila("Concepto") & ", ISAE=" & isae.ToString & ", CONCEPTO2='" & fila("Concepto2") & "', REF='" & fila("Ref") & "', POLIZA=" & poliza.ToString & ", CARGO='" & fila("Cargo") & "' , ABONO='" & fila("Abono") & "', DEPTO=" & fila("Depto") & " WHERE ID=" & fila(0))


            End If
        Next

        MessageBox.Show("Movimientos actualizados", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Public Sub Genera_Poliza()
        Dim col As String = ""
        Dim Depto As Integer = 0

        Dim PP As New Poliza_preview
        For Each fila As DataGridViewRow In dgvRes.Rows
            If Not fila.Cells("Concepto").Value Is DBNull.Value Then
                cuenta = fila.Cells("Cuenta").Value
                empresa = CType(dtCuentas.Select("NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("EMP")
                base = CType(dtCuentas.Select("NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("EMPRESA")

                If fila.Cells("Depositos").Value = 0 Then
                    tipo_mov = "C"
                Else
                    tipo_mov = "A"
                End If

                Dim concepto As String = con.QueryGetFieldTmpCnn("SELECT CONCEP FROM " & base.ToUpper & ".dbo.COMD" & Format(empresa, "00") & " WHERE NUM_REG=" & fila.Cells("Concepto").Value, cst_Papalote)
                Dim concepto2 As String = CType(dtCuentas.Select("NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("BANCO") & " CTA " & CType(dtCuentas.Select("NUM_CTA='" & cuenta & "'").GetValue(0), DataRow)("NUM_CTA")

                Dim doc As Poliza_preview.Documento
                If tipo_mov = "C" Then
                    doc = New Poliza_preview.Documento(concepto, concepto2, fila.Cells("Retiros").Value, fila.Cells("Cargo").Value, fila.Cells("Abono").Value, fila.Cells("Depto").Value)
                Else
                    doc = New Poliza_preview.Documento(concepto2, concepto, fila.Cells("Depositos").Value, fila.Cells("Cargo").Value, fila.Cells("Abono").Value, fila.Cells("Depto").Value)
                End If

                PP.Agrega_Doc(doc)
            End If

        Next
        PP.Mostrar()
    End Sub

    Private Sub btnPolizaN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPolizaN.Click
        Genera_Poliza()
    End Sub

    Private Sub btnGuardarBancos_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardarBancos.Click
        Dim con1 As New Conexion_SQL(cst_Papalote)
        con1.ConnectDataBase()

        Dim con2 As New Conexion_SQL(cst_Bancos)
        con2.ConnectDataBase()

        For Each fila As DataRow In dtRes.Rows
            If Not (fila("Concepto") Is DBNull.Value OrElse fila("Concepto").ToString.Trim.Length = 0) Then

                Dim cobrado As String = ""
                Dim monto As Double = 0
                If fila("Depositos") = 0 Then
                    tipo_mov = "C"
                    cobrado = "S"
                    monto = fila("Retiros")
                Else
                    tipo_mov = "A"
                    cobrado = "N"
                    monto = fila("Depositos")
                End If

                Dim Cuenta As String = fila("Cuenta")
                Dim Empresa As String = Format(Val(CType(dtCuentas.Select("NUM_CTA='" & Cuenta & "'").GetValue(0), DataRow)("EMP")), "00")
                Dim IdCuenta As String = Format(Val(CType(dtCuentas.Select("NUM_CTA='" & Cuenta & "'").GetValue(0), DataRow)("CVE_CTA")), "00")
                Dim Base As String = CType(dtCuentas.Select("NUM_CTA='" & Cuenta & "'").GetValue(0), DataRow)("EMPRESA")
                Dim Num_Reg As Integer = Val(con2.QueryGetFieldTmpCnn("SELECT NUM_REGS FROM " & Base & ".dbo.MOV0" & IdCuenta & Empresa)) + 1

                Dim cad As String = ""
                cad += "INSERT INTO " & Base & ".dbo.MOVS" & IdCuenta & Empresa & " (NUM_REG,TIP_REG,CON_PART,CTA_BAN,CVE_CONCEP,NUM_CHEQUE,REF,STATUS,FECHA,F_COBRO,TIPO,BAND_PRN,BAND_CONT,BAND_CONC,ACT_SAE,NUM_POL,TIP_POL,COBRADO,"
                cad += vbCrLf & "SAE_COI,MONTO_TOT,T_CAMBIO,HORA,CLPV,FACT,REFER,MONTO_DOC,CTA_TRANSF,TRANSFER,BENEF,OBSER,CVE_INST,MONDIFSAE,TCAMBIOSAE,RFC,IVA,MONIVAACR,CONC_SAE,SOLICIT,TRANS_COI,INTSAENOI)"
                cad += vbCrLf & "VALUES(" & Num_Reg
                cad += vbCrLf & ",0" 'TIP_REG
                cad += vbCrLf & ",0" 'CON_PART
                cad += vbCrLf & "," & IdCuenta 'CTA_BAN
                cad += vbCrLf & ",'" & con.QueryGetFieldTmpCnn("SELECT CVE_CONCEP FROM " & Base & ".dbo.COMD" & Empresa & " WHERE NUM_REG=" & fila("Concepto")) & "'" 'CVE_CONCEP
                cad += vbCrLf & ",0" 'NUM_CHEQUE
                cad += vbCrLf & ",'" & fila("REF") & "'" 'REF
                cad += vbCrLf & ",'A'" 'STATUS
                cad += vbCrLf & ",GETDATE()" 'FECHA
                cad += vbCrLf & ",GETDATE()" 'F_COBRO
                cad += vbCrLf & ",'" & tipo_mov & "'" 'TIPO
                cad += vbCrLf & ",''" 'BAND_PRN
                cad += vbCrLf & ",''" 'BAND_CONT
                cad += vbCrLf & ",''" 'BAND_CONC
                cad += vbCrLf & ",''" 'ACT_SAE
                cad += vbCrLf & ",''" 'NUM_POL
                cad += vbCrLf & ",''" 'TIP_POL
                cad += vbCrLf & ",'" & cobrado & "'" 'COBRADO
                cad += vbCrLf & ",0" 'SAE_COI
                cad += vbCrLf & "," & monto 'MONTO_TOT
                cad += vbCrLf & ",1" 'T_CAMBIO
                cad += vbCrLf & ",0" 'HORA

                If Not fila("CONCEPTO2") Is DBNull.Value AndAlso fila("CONCEPTO2").ToString.Trim.Length > 0 Then
                    cad += vbCrLf & ",'" & agrega_espacios(fila("CONCEPTO2"), 5) & "'" 'CLPV
                    cad += vbCrLf & ",'" & agrega_espacios(fila("REF"), 7) & "'" 'FACT
                    cad += vbCrLf & ",'" & agrega_espacios(fila("REF"), 7) & "'" 'REFER
                Else
                    cad += vbCrLf & ",''" 'CLPV
                    cad += vbCrLf & ",''" 'FACT
                    cad += vbCrLf & ",''" 'REFER
                End If

                cad += vbCrLf & "," & monto 'MONTO_DOC
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",'N'"
                cad += vbCrLf & ",''"
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",''"
                cad += vbCrLf & ",1"
                cad += vbCrLf & ",''"
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",''"
                cad += vbCrLf & ",0"
                cad += vbCrLf & ",0)"

                con2.ExecuteSQL(cad)
                con2.QueryGetFieldTmpCnn("UPDATE " & Base & ".dbo.MOV0" & IdCuenta & Empresa & " SET NUM_REGS=" & Num_Reg)
                con1.ExecuteSQL("UPDATE ECUENTAS SET STATUS=0 WHERE ID=" & fila(0))
            End If
        Next

        Carga_estados()
        MessageBox.Show("Movimientos guardados correctamente", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Function agrega_espacios(ByVal cad As String, ByVal cantidad As Integer) As String
        For i As Integer = cad.Length To cantidad - 1
            cad = " " & cad
        Next
        Return cad
    End Function

End Class